﻿#include "businesssql.h"
#include <iostream>
#include <Debug/DEBUG.hpp>
#include <FileManager/filemanager.h>
#include <AppConfig/appconfig.h>
#include <format>
#include <iomanip>

BusinessSQL::BusinessSQL() {}
// 连接数据库服务器，检查目标数据库是否存在，如果不存在，则创建该数据库，如果存在，则使用该数据库
void BusinessSQL::init()
{
    AppConfig& config = AppConfig::getInstance();
    std::string host = config.globalValue("SQLHostName");
    std::string port = config.globalValue("SQLPort");
    std::string userName = config.globalValue("SQLUserName");
    std::string userPwd = config.globalValue("SQLUserPwd");
    std::string sql = "host=" + host + " port=" + port + " user=" + userName + " password=" + userPwd;
    DEBUG_INFO(sql);
    dbPool = std::make_shared<soci::connection_pool>(POOL_SIZE);
    // 初始化连接池
    for (size_t i = 0; i < POOL_SIZE; i++) {
        soci::session &db = dbPool->at(i);
        db.open(soci::mysql, sql);
    }
}

void BusinessSQL::useDatabase() {
    AppConfig& config = AppConfig::getInstance();
    std::string dbname = "server_database";
    // 检查目标数据库是否存在
    bool flag = false;
    try {
        soci::session db(*dbPool);
        int dbCount = 0;
        db << "SELECT COUNT(SCHEMA_NAME) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = :dbName", soci::use(dbname), soci::into(dbCount);
        if (dbCount == 0) {
            // 数据库不存在，创建数据库
            db << "CREATE DATABASE " + dbname + " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
            flag = false;
        } else {
            flag = true;
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }

    // 初始化连接池中的所有的连接
    for (size_t i = 0; i < POOL_SIZE; i ++) {
        soci::session &db = dbPool->at(i);
        std::string sql = "USE " + dbname;
        db << sql;
    }
    // 如果是新创建的数据库，则初始化数据库
    if (!flag) {
        createTable();
    }
}


void BusinessSQL::createTable()
{
    std::string sqls[7];
    sqls[0] = R"(CREATE TABLE `users` (
                user_id INT PRIMARY KEY AUTO_INCREMENT,
                username VARCHAR(255) NOT NULL UNIQUE, -- 确保用户名是唯一的
                password VARCHAR(255) NOT NULL, -- 密码应该加密存储
                avatar INT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                );)";
    sqls[1] = R"(CREATE TABLE `friendships` (
                user_id1 INT NOT NULL,
                user_id2 INT NOT NULL,
                status VARCHAR(10) NOT NULL CHECK (status IN ('pending', 'accepted', 'declined', 'blocked')), -- 使用 CHECK 约束代替 ENUM
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (user_id1, user_id2),
                FOREIGN KEY (user_id1) REFERENCES users(user_id),
                FOREIGN KEY (user_id2) REFERENCES users(user_id)
                );)";
    sqls[2] = R"(CREATE TABLE `groups` (
                group_id INT PRIMARY KEY AUTO_INCREMENT,
                group_name VARCHAR(255) NOT NULL,
                owner_id INT NOT NULL, -- 群主ID
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (owner_id) REFERENCES users(user_id)
                );)";
    sqls[3] = R"(CREATE TABLE `group_members` (
                group_id INT NOT NULL,
                user_id INT NOT NULL,
                status VARCHAR(10) NOT NULL CHECK (status IN ('pending', 'member', 'left')), -- 使用 CHECK 约束代替 ENUM
                joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (group_id, user_id),
                FOREIGN KEY (group_id) REFERENCES `groups`(group_id),
                FOREIGN KEY (user_id) REFERENCES users(user_id)
                );)";
    sqls[4] = R"(CREATE TABLE `user_messages` (
                message_id INT PRIMARY KEY AUTO_INCREMENT,
                sender_id INT NOT NULL,
                receiver_id INT NOT NULL,
                message TEXT NOT NULL,
                status VARCHAR(10) NOT NULL CHECK (status IN ('sended', 'pending')), -- 使用 CHECK 约束代替 ENUM
                type INT NOT NULL,
                sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (sender_id) REFERENCES users(user_id),
                FOREIGN KEY (receiver_id) REFERENCES users(user_id)
                );)";
    sqls[5] = R"(CREATE TABLE `group_messages` (
                message_id INT PRIMARY KEY AUTO_INCREMENT,
                group_id INT NOT NULL,
                sender_id INT NOT NULL,
                message TEXT NOT NULL,
                type INT NOT NULL,
                sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (group_id) REFERENCES `groups`(group_id),
                FOREIGN KEY (sender_id) REFERENCES users(user_id)
                );)";
    sqls[6] = R"(CREATE TABLE `file` (
                file_id INT PRIMARY KEY AUTO_INCREMENT,
                name TEXT,
                file_size INT,
                type INT, -- type为1时，是普通的文件，2为聊天图片
                upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                );)";
    soci::session db(*dbPool);
    for (int i = 0 ; i < 7; i ++) {
        try {
            db << sqls[i].c_str();
        } catch (const soci::soci_error& e) {
            std::cerr << "SOCI error: " << e.what() << std::endl;
        } catch (const std::exception& e) {
            std::cerr << "Error: " << e.what() << std::endl;
        }
    }
}

void BusinessSQL::createAUser(const std::string &account, const std::string &password)
{
    soci::session db(*dbPool);
    try {
        db << "INSERT INTO users (username, password, avatar) VALUES (:account, :password, 1);", soci::use(account), soci::use(password);
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}


nlohmann::json BusinessSQL::queryAllTheUserOfTargetRelation(const std::string &account, const std::string& relation)
{
    soci::session db(*dbPool);
    nlohmann::json result;
    // sql
    std::string sql = "SELECT u.user_id, u.username, u.avatar, f.status "
                      "FROM users u, friendships f "
                      "WHERE u.user_id = f.user_id1 AND f.user_id2 = (SELECT user_id FROM users WHERE username = :account) AND f.status = :relation";

    try {
        soci::rowset<soci::row> rs = (db.prepare << sql.c_str(), soci::use(account), soci::use(relation));
        for (auto it = rs.begin(); it != rs.end(); it ++) {
            const soci::row& row = *it;
            nlohmann::json friend_info;
            friend_info["UserID"] = row.get<int>("user_id");
            friend_info["Account"] = row.get<std::string>("username");
            friend_info["Avatar"] = row.get<int>("avatar");
            friend_info["Relation"] = row.get<std::string>("status");
            result.push_back(friend_info);
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    return result;
}

nlohmann::json BusinessSQL::queryAUser(uint64_t id)
{
    soci::session db(*dbPool);
    nlohmann::json result;
    // sql
    std::string sql = "SELECT user_id, username, avatar "
                      "FROM users "
                      "WHERE user_id = :id";
    soci::row ans;
    try {
        db << sql.c_str(), soci::into(ans), soci::use(id);
        // 检查是否有结果
        if (!db.got_data()) {
            std::cerr << "No data found." << std::endl;
            return result;
        }

        result["UserID"] = ans.get<int>("user_id");
        result["Account"] = ans.get<std::string>("username");
        result["Avatar"] = ans.get<int>("avatar");
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    return result;
}

nlohmann::json BusinessSQL::queryAUser(const std::string &account)
{
    soci::session db(*dbPool);
    nlohmann::json result;
    // sql
    std::string sql = "SELECT user_id, username, avatar "
                      "FROM users "
                      "WHERE username = :account";
    soci::row ans;
    try {
        db << sql.c_str(), soci::into(ans), soci::use(account);
        // 检查是否有结果
        if (!db.got_data()) {
            std::cerr << "No data found." << std::endl;
            return result;
        }

        result["UserID"] = ans.get<int>("user_id");
        result["Account"] = ans.get<std::string>("username");
        result["Avatar"] = ans.get<int>("avatar");
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    return result;
}

nlohmann::json BusinessSQL::quertAUserToUser(const std::string &applyAccount, const std::string &targetAccount)
{
    nlohmann::json user1 = queryAUser(applyAccount);
    nlohmann::json user2 = queryAUser(targetAccount);

    DEBUG_INFO(user1.dump());
    DEBUG_INFO(user2.dump());

    std::string relation = getRelation(user2["UserID"], user1["UserID"]);
    user2["Relation"] = relation;

    return user2;
}

nlohmann::json BusinessSQL::queryOffineMessage(uint64_t senderID, uint64_t receiverID)
{
    soci::session db(*dbPool);
    nlohmann::json result;
    // sql
    std::string sql = "SELECT * "
                      "FROM user_messages "
                      "WHERE sender_id = :send_id "
                      "AND receiver_id = :receive_id "
                      "AND status = 'pending'";

    try {
        soci::rowset<soci::row> rs = (db.prepare << sql.c_str(), soci::use(senderID), soci::use(receiverID));

        for (auto it = rs.begin(); it != rs.end(); it ++) {
            const soci::row& row = *it;
            nlohmann::json message_info;
            message_info["MessageID"] = row.get<int>("message_id");
            message_info["Message"] = row.get<std::string>("message");
            message_info["MessageType"] = row.get<int>("type");
            std::tm when = row.get<std::tm>("sent_at");
            std::ostringstream timeStringStream;
            timeStringStream << std::put_time(&when, "%Y-%m-%d %H:%M:%S");
            message_info["Time"] = timeStringStream.str();
            result.push_back(message_info);
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    return result;
}

void BusinessSQL::storeOfflineMessage(uint64_t senderID, uint64_t receiverID, std::string message)
{
    soci::session db(*dbPool);
    std::string sql = "INSERT INTO user_messages (sender_id, receiver_id, message, status)"
                      "VALUES (:senderID, :receiverID, :message, 'pending');";
    try {
        // 执行插入操作
        db << sql.c_str(),
            soci::use(senderID),
            soci::use(receiverID),
            soci::use(message);
    } catch (const soci::soci_error& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Exception: " << e.what() << std::endl;
    }
}

void BusinessSQL::updateOfflineMessageStatus(int messageID)
{
    soci::session db(*dbPool);
    std::string sql = "UPDATE user_messages "
                      "SET status = 'sended' "
                      "WHERE message_id = :messageID;";
    try {
        // 执行插入操作
        db << sql.c_str(),
            soci::use(messageID);
    } catch (const soci::soci_error& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Exception: " << e.what() << std::endl;
    }
}

bool BusinessSQL::verifyAUser(const std::string &account, const std::string &password)
{
    soci::session db(*dbPool);
    // sql
    std::string sql = "SELECT password "
                      "FROM users "
                      "WHERE username = :account";

    soci::row ans;
    try {
        db << sql.c_str(), soci::into(ans), soci::use(account);
        // 检查是否有结果
        if (!db.got_data()) {
            std::cerr << "No data found." << std::endl;
            return false;
        }

        std::string resultPsw = ans.get<std::string>("password");

        if (resultPsw == password)
            return true;
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }

    return false;
}

nlohmann::json BusinessSQL::queryAllTheFile()
{
    soci::session db(*dbPool);
    nlohmann::json result;
    std::string sql = "SELECT file_id, name FROM file WHERE type = 1;";
    try {
        soci::rowset<soci::row> rs = (db.prepare << sql.c_str());

        for (auto it = rs.begin(); it != rs.end(); it ++) {
            const soci::row& row = *it;
            nlohmann::json friend_info;
            friend_info["FileID"] = row.get<int>("file_id");
            friend_info["Name"] = row.get<std::string>("name");
            result.push_back(friend_info);
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    DEBUG_INFO(result.dump());
    return result;
}

bool BusinessSQL::queryAFileExists(const std::string &fileName, int type)
{
    soci::session db(*dbPool);
    bool result = false;
    std::string sql = "SELECT COUNT(*) FROM file WHERE name = :fileName AND type = :type";

    int count;

    // 执行SQL查询
    try {
        db << sql.c_str(), soci::into(count), soci::use(fileName), soci::use(type);

        // 检查是否找到了文件
        if (count > 0) {
            result = true;
        } else {
            result = false;
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
        return result;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
        return result;
    }
    return result;
}

void BusinessSQL::insertAFile(const std::string &fileName, uint64_t fileSize, int type)
{
    soci::session db(*dbPool);
    if (fileName.empty()) {
        throw std::runtime_error("empty file error");
    }
    std::string sql = "INSERT INTO file (name, file_size, type) VALUES (:fileName, :fileSize, :type);";

    try {
        db << sql.c_str(), soci::use(fileName), soci::use(fileSize), soci::use(type);
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

nlohmann::json BusinessSQL::queryAFile(uint64_t id)
{
    soci::session db(*dbPool);
    nlohmann::json result;
    std::string sql = "SELECT * FROM file WHERE file_id = :fileID AND type = 1;";
    soci::row ans;
    try {
        db << sql.c_str(), soci::into(ans), soci::use(id);
        // 检查是否有结果
        if (!db.got_data()) {
            std::cerr << "No data found." << std::endl;
            return result;
        }
        result["FileID"] = ans.get<int>("file_id");
        result["FileName"] = ans.get<std::string>("name");
        result["FileSize"] = ans.get<int>("file_size");
        result["Type"] = ans.get<int>("type");
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    return result;
}

nlohmann::json BusinessSQL::queryAPicture(const std::string& fileName)
{
    DEBUG_INFO("查找以下图片：" + fileName);
    soci::session db(*dbPool);
    nlohmann::json result;
    std::string sql = "SELECT * FROM file WHERE name = :fileName AND type = 2;";
    soci::row ans;
    try {
        db << sql.c_str(), soci::into(ans), soci::use(fileName);
        // 检查是否有结果
        if (!db.got_data()) {
            std::cerr << "No data found." << std::endl;
            return result;
        }
        result["FileID"] = ans.get<int>("file_id");
        result["FileName"] = ans.get<std::string>("name");
        result["FileSize"] = ans.get<int>("file_size");
        result["Type"] = ans.get<int>("type");
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    return result;
}

void BusinessSQL::updateOrInsertFriendship(uint64_t applyID, uint64_t targetID, const std::string &status)
{
    soci::session db(*dbPool);
    std::string sql = "UPDATE friendships SET status = :status WHERE user_id1 = :applyID AND user_id2 = :targetID;";

    try {
        // 首先尝试更新
        soci::statement updateStm = (db.prepare << sql.c_str(),
                                     soci::use(status), soci::use(applyID), soci::use(targetID));

        updateStm.execute();
        if (updateStm.get_affected_rows() == 0) {
            sql = "INSERT INTO friendships (user_id1, user_id2, status) VALUES (:applyID, :targetID, :status)";

            // 更新没有影响任何行，尝试插入
            db << sql.c_str(),
                soci::use(applyID), soci::use(targetID), soci::use(status);
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

std::string BusinessSQL::getRelation(uint64_t user1, uint64_t user2)
{
    soci::session db(*dbPool);
    std::string sql = "SELECT status "
                      "FROM friendships "
                      "WHERE (user_id1 = :user1 AND user_id2 = :user2);";

    std::string result;
    try {
        db << sql.c_str(), soci::into(result), soci::use(user1), soci::use(user2);
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    return result;
}

void BusinessSQL::eraseFriend(uint64_t user1ID, uint64_t user2ID)
{
    soci::session db(*dbPool);
    std::string sql = "DELETE FROM friendships "
                      "WHERE (user_id1 = :user1ID AND user_id2 = :user2ID) "
                      "OR (user_id1 = :user2ID AND user_id2 = :user1ID);";

    try {
        db << sql.c_str(), soci::use(user1ID), soci::use(user2ID), soci::use(user2ID), soci::use(user1ID);
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}
/*
std::pair<uint64_t, std::string> BusinessSQL::insertUserMessage(uint64_t senderID, uint64_t recvID, const std::string &message, const std::string& status)
{
    sqlite3_stmt * stmt;
    char *errMsg = nullptr;
    int rc;
    // 开始事务
    rc = sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, &errMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "SQL error: " << errMsg << std::endl;
        sqlite3_free(errMsg);
        return {};
    }

    // 准备插入语句
    std::string sql = "INSERT INTO user_messages (sender_id, receiver_id, message, status) VALUES (?, ?, ?, ?);";
    rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        std::cerr << "Preparation failed: " << sqlite3_errmsg(db) << std::endl;
        return {};
    }

    // 绑定参数
    sqlite3_bind_int(stmt, 1, senderID);
    sqlite3_bind_int(stmt, 2, recvID);
    sqlite3_bind_text(stmt, 3, message.c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 4, status.c_str(), -1, SQLITE_STATIC);

    // 执行插入
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        std::cerr << "Insertion failed: " << sqlite3_errmsg(db) << std::endl;
        sqlite3_finalize(stmt);
        return {};
    }

    // 清理
    sqlite3_finalize(stmt);

    // 获取最后插入的行的ID
    uint64_t messageId = static_cast<int>(sqlite3_last_insert_rowid(db));

    // 获取插入消息的时间
    sql = "SELECT sent_at FROM user_messages WHERE message_id = ?;";
    rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        std::cerr << "Preparation failed: " << sqlite3_errmsg(db) << std::endl;
        return {};
    }

    sqlite3_bind_int(stmt, 1, messageId);
    // send time
    std::string sentAt;
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        sentAt = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
    } else {
        std::cerr << "Failed to retrieve sent_at time: " << sqlite3_errmsg(db) << std::endl;
        sqlite3_finalize(stmt);
        return {};
    }

    // 清理
    sqlite3_finalize(stmt);

    // 提交事务
    rc = sqlite3_exec(db, "COMMIT;", NULL, NULL, &errMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "SQL error: " << errMsg << std::endl;
        sqlite3_free(errMsg);
        return {};
    }

    return {messageId, sentAt}; // 成功
}
*/
std::pair<uint64_t, std::string> BusinessSQL::insertUserMessage(uint64_t senderID, uint64_t recvID, const std::string &message, const std::string& status, int messageType)
{
    soci::session db(*dbPool);
    try {
        // 开始事务
        db.begin();

        // 准备插入语句
        std::string sentAt;
        uint64_t messageId = 0;

        // 第一步是插入数据
        db << "INSERT INTO user_messages (sender_id, receiver_id, message, status, type) VALUES (:sender, :receiver, :message, :status, :messageType)",
            soci::use(senderID, "sender"),
            soci::use(recvID, "receiver"),
            soci::use(message, "message"),
            soci::use(status, "status"),
            soci::use(messageType, "messageType");

        // 第二步是获取刚刚插入行的ID和sent_at
        db << "SELECT LAST_INSERT_ID(), NOW()", soci::into(messageId), soci::into(sentAt);

        // 提交事务
        db.commit();

        return {messageId, sentAt}; // 成功
    } catch (const soci::soci_error& e) {
        // 输出错误信息
        DEBUG_INFO("SQL error: " << e.what());

        // 回滚事务
        try {
            db.rollback();
        } catch (...) {
            DEBUG_INFO("SQL error: rollback failed");
        }

        return {};
    }
    return {};
}

void BusinessSQL::updateAvatar(uint64_t userID, int avatar)
{
    soci::session db(*dbPool);
    std::string sql = "UPDATE users SET avatar = :avatar WHERE user_id = :userID;";
    try {
        db << sql.c_str(), soci::use(avatar), soci::use(userID);
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

bool BusinessSQL::queryAGroupExists(const std::string &groupName)
{
    soci::session db(*dbPool);
    bool result = false;
    std::string sql = "SELECT COUNT(*) FROM `groups` WHERE group_name = :groupName";

    int count;
    // 执行SQL查询
    try {
        db << sql.c_str(), soci::into(count), soci::use(groupName);

        // 检查是否找到了文件
        if (count > 0) {
            result = true;
        } else {
            result = false;
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
        return result;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
        return result;
    }

    return result;
}

nlohmann::json BusinessSQL::queryAGroupInfo(const std::string &groupName)
{
    soci::session db(*dbPool);
    nlohmann::json result;
    std::string sql = "SELECT group_id, group_name, owner_id FROM `groups` WHERE group_name = :groupName";

    soci::row ans;
    try {
        db << sql.c_str(), soci::into(ans), soci::use(groupName);
        // 检查是否有结果
        if (!db.got_data()) {
            std::cerr << "No data found." << std::endl;
            return result;
        }
        result["GroupID"] = ans.get<int>("group_id");
        result["GroupName"] = ans.get<std::string>("group_name");
        result["OwnerID"] = ans.get<int>("owner_id");
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    return result;
}

void BusinessSQL::createAGroup(const std::string &groupName, uint64_t userID)
{
    soci::session db(*dbPool);
    std::string sql = "INSERT INTO `groups` (group_name, owner_id) VALUES (:groupName, :userID);";

    try {
        db << sql.c_str(), soci::use(groupName), soci::use(userID);
    } catch (const soci::soci_error& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
        return;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
        return;
    }
}

nlohmann::json BusinessSQL::findUserGroupsAndPosition(const std::string &account)
{
    soci::session db(*dbPool);
    nlohmann::json result;
    std::string sql = "SELECT g.group_id, g.group_name "
                      "FROM `groups` AS g "
                      "JOIN `group_members` AS gm ON g.group_id = gm.group_id "
                      "JOIN `users` AS u ON gm.user_id = u.user_id "
                      "WHERE u.username = :account AND gm.status = 'member'";
    try {
        soci::rowset<soci::row> rs = (db.prepare << sql, soci::use(account));

        for (auto it = rs.begin(); it != rs.end(); it ++) {
            const soci::row& row = *it;
            nlohmann::json group_info;
            group_info["GroupID"] = row.get<int>("group_id");
            group_info["GroupName"] = row.get<std::string>("group_name");
            DEBUG_INFO(group_info.dump());
            result.push_back(group_info);
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    return result;
}

void BusinessSQL::joinAGroup(uint64_t groupID, uint64_t userID, std::string status)
{
    soci::session db(*dbPool);
    std::string sqlInsert = "INSERT IGNORE INTO group_members (group_id, user_id, status) "
                            "VALUES (:groupID, :userID, :status);";
    try {
        // 执行SQL语句
        soci::statement st = (db.prepare << sqlInsert.c_str(), soci::use(groupID), soci::use(userID), soci::use(status));
        st.execute(true);

        // 检查是否成功
        // 如果没有成功插入，表示当前已经有一个记录了，因此要更改状态
        if (st.get_affected_rows() == 0) {
            std::string sqlUpdate = "UPDATE group_members SET status = :status "
                                    "WHERE group_id = :groupID AND user_id = :userID AND status != 'member';";

            db << sqlUpdate, soci::use(status), soci::use(groupID), soci::use(userID);
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "SQL error: " << e.what() << std::endl;
        return;
    }
}

std::pair<uint64_t, std::string> BusinessSQL::storeGroupMessage(uint64_t userID, uint64_t groupID, const std::string &message, int messageType)
{
    soci::session db(*dbPool);
    uint64_t messageID;
    std::string sentAt;
    try {
        soci::transaction tr(db);

        // 插入消息
        db << "INSERT INTO group_messages (group_id, sender_id, message, type) VALUES (:group_id, :sender_id, :message, :messageType)",
            soci::use(groupID, "group_id"), soci::use(userID, "sender_id"), soci::use(message, "message"), soci::use(messageType, "messageType");

        // 获取最新的自增ID
        db << "SELECT LAST_INSERT_ID()", soci::into(messageID);
        // 获取发送的时间
        db << "SELECT sent_at FROM group_messages WHERE message_id = :message_id",
            soci::into(sentAt), soci::use(messageID, "message_id");

        // 提交事务
        tr.commit();

    } catch (const soci::soci_error& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
    }
    return {messageID, sentAt};
}

nlohmann::json BusinessSQL::getGroupMember(uint64_t groupID)
{
    soci::session db(*dbPool);
    nlohmann::json result;
    std::string sql = "SELECT user_id FROM group_members WHERE group_id = :groupID;";
    try {
        soci::rowset<soci::row> rs = (db.prepare << sql.c_str(), soci::use(groupID));

        for (auto it = rs.begin(); it != rs.end(); it ++) {
            const soci::row& row = *it;
            nlohmann::json userInfo;
            userInfo["UserID"] = row.get<int>("user_id");
            result.push_back(userInfo);
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }

    return result;
}

void BusinessSQL::userLeaveGroup(uint64_t groupID, uint64_t userID)
{
    soci::session db(*dbPool);
    std::string sql = "UPDATE group_members "
                      "SET status = 'left' "
                      "WHERE group_id = :groupID AND user_id = :userID;";

    try {
        db << sql.c_str(), soci::use(groupID), soci::use(userID);
    } catch (const soci::soci_error& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

void BusinessSQL::deleteGroupMessage(uint64_t groupID)
{
    soci::session db(*dbPool);
    std::string sql = "DELETE FROM group_messages WHERE group_id = :groupID;";

    try {
        db << sql.c_str(), soci::use(groupID);
    } catch (const soci::soci_error& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

void BusinessSQL::deleteGroupMember(uint64_t groupID)
{
    soci::session db(*dbPool);
    std::string sql = "DELETE FROM group_members WHERE group_id = :groupID;";

    try {
        db << sql.c_str(), soci::use(groupID);
    } catch (const soci::soci_error& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

void BusinessSQL::deleteGroup(uint64_t groupID)
{
    soci::session db(*dbPool);
    deleteGroupMessage(groupID);
    deleteGroupMember(groupID);

    std::string sql = "DELETE FROM `groups` WHERE group_id = :groupID;";

    try {
        db << sql.c_str(), soci::use(groupID);
    } catch (const soci::soci_error& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

void BusinessSQL::acceptUserJoinGroupRequest(uint64_t groupID, uint64_t userID)
{
    soci::session db(*dbPool);
    std::string sql = "UPDATE group_members "
                      "SET status = 'member' "
                      "WHERE group_id = :groupID AND user_id = :userID;";

    try {
        db << sql.c_str(), soci::use(groupID), soci::use(userID);
    } catch (const soci::soci_error& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

void BusinessSQL::rejectUserJoinGroupRequest(uint64_t groupID, uint64_t userID)
{
    soci::session db(*dbPool);
    std::string sql = "DELETE FROM group_members WHERE group_id = :groupID AND user_id = :userID;";

    try {
        db << sql.c_str(), soci::use(groupID), soci::use(userID);
    } catch (const soci::soci_error& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

nlohmann::json BusinessSQL::getAllGroupRequest(const std::string& ownerName)
{
    soci::session db(*dbPool);
    nlohmann::json result;
    std::string sql = "SELECT u.username, gm.group_id "
                      "FROM users AS u "
                      "JOIN group_members AS gm ON u.user_id = gm.user_id "
                      "JOIN `groups` AS g ON gm.group_id = g.group_id "
                      "WHERE g.owner_id = (SELECT user_id FROM users WHERE username = :ownerName) "
                      "AND gm.status = 'pending';";
    try {
        soci::rowset<soci::row> rs = (db.prepare << sql.c_str(), soci::use(ownerName));

        for (auto it = rs.begin(); it != rs.end(); it ++) {
            const soci::row& row = *it;
            nlohmann::json userInfo;
            userInfo["GroupID"] = row.get<int>("group_id");
            userInfo["UserAccount"] = row.get<std::string>("username");
            result.push_back(userInfo);
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    DEBUG_INFO("check:");
    DEBUG_INFO(result.dump());
    return result;
}

nlohmann::json BusinessSQL::getLatest30GroupMessage(uint64_t groupID)
{
    soci::session db(*dbPool);
    nlohmann::json result;
    std::string sql = "SELECT group_id, sender_id, message, sent_at "
                      "FROM group_messages "
                      "WHERE group_id = :groupID "
                      "ORDER BY sent_at DESC "
                      "LIMIT 30;";

    try {
        soci::rowset<soci::row> rs = (db.prepare << sql.c_str(), soci::use(groupID));

        for (auto it = rs.begin(); it != rs.end(); it ++) {
            const soci::row& row = *it;
            nlohmann::json messageInfo;
            messageInfo["GroupID"] = row.get<int>("group_id");
            messageInfo["UserID"] = row.get<int>("sender_id");
            messageInfo["Message"] = row.get<std::string>("message");

            std::tm when = row.get<std::tm>("sent_at");
            std::ostringstream timeStringStream;
            timeStringStream << std::put_time(&when, "%Y-%m-%d %H:%M:%S");
            messageInfo["SendTime"] = timeStringStream.str();
            result.push_back(messageInfo);
        }
    } catch (const soci::soci_error& e) {
        std::cerr << "SOCI error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    return result;
}
